﻿using System;
using System.Text;
using Unknown.Framework.Database;
using Unknown.Source.Entity.Example;
using Unknown.Source.Sql.Example;

namespace Unknown.Source.OracleSql.Example
{
	/// <summary>
	/// 艺术品Oracle SQL抽象类
	/// </summary>
	public abstract class ArtworkOracleSql : IArtworkSql
	{
		/// <summary>
		/// 构建新增
		/// </summary>
		/// <param name="entity">实体</param>
		/// <returns>操作</returns>
		public virtual Operation BuildInsert(Artwork entity)
		{
			Operation result = new Operation();

			if (entity != null)
			{
				NoQueryOperationHandler operationHandler = new NoQueryOperationHandler();

				result.Sql = @"
INSERT INTO Z_ARTWORK T
  (T.PKID,
   T.MUSEUM,
   T.NAME,
   T.CODE,
   T.ENGLISH_DESCRIPTION,
   T.ENGLISH_REMARK,
   T.CHINESE_DESCRIPTION,
   T.CHINESE_REMARK,
   T.PHOTO,
   T.VIDEO,
   T.FIRST_CARE_TIME,
   T.LAST_CARE_TIME,
   T.FIRST_SHOW_TIME,
   T.LAST_SHOW_TIME,
   T.CARE_TIMES,
   T.SHOW_TIMES,
   T.PHOTO_VISIT,
   T.VIDEO_VISIT,
   T.COST,
   T.VALUE)
VALUES
  (:PKID,
   :MUSEUM,
   :NAME,
   :CODE,
   :ENGLISH_DESCRIPTION,
   :ENGLISH_REMARK,
   :CHINESE_DESCRIPTION,
   :CHINESE_REMARK,
   :PHOTO,
   :VIDEO,
   :FIRST_CARE_TIME,
   :LAST_CARE_TIME,
   :FIRST_SHOW_TIME,
   :LAST_SHOW_TIME,
   :CARE_TIMES,
   :SHOW_TIMES,
   :PHOTO_VISIT,
   :VIDEO_VISIT,
   :COST,
   :VALUE)
";

				result.Parameters.Add(new Parameter("PKID", entity.Pkid, "Varchar2"));
				result.Parameters.Add(new Parameter("MUSEUM", entity.Museum, "Varchar2"));
				result.Parameters.Add(new Parameter("NAME", entity.Name, "NVarchar2"));
				result.Parameters.Add(new Parameter("CODE", entity.Code, "NVarchar2"));
				result.Parameters.Add(new Parameter("ENGLISH_DESCRIPTION", entity.EnglishDescription, "Clob"));
				result.Parameters.Add(new Parameter("ENGLISH_REMARK", entity.EnglishRemark, "Clob"));
				result.Parameters.Add(new Parameter("CHINESE_DESCRIPTION", entity.ChineseDescription, "NClob"));
				result.Parameters.Add(new Parameter("CHINESE_REMARK", entity.ChineseRemark, "NClob"));
				result.Parameters.Add(new Parameter("PHOTO", entity.Photo, "Blob"));
				result.Parameters.Add(new Parameter("VIDEO", entity.Video, "Blob"));
				result.Parameters.Add(new Parameter("FIRST_CARE_TIME", entity.FirstCareTime, "TimeStamp"));
				result.Parameters.Add(new Parameter("LAST_CARE_TIME", entity.LastCareTime, "TimeStamp"));
				result.Parameters.Add(new Parameter("FIRST_SHOW_TIME", entity.FirstShowTime, "Date"));
				result.Parameters.Add(new Parameter("LAST_SHOW_TIME", entity.LastShowTime, "Date"));
				result.Parameters.Add(new Parameter("CARE_TIMES", entity.CareTimes, "Int32"));
				result.Parameters.Add(new Parameter("SHOW_TIMES", entity.ShowTimes, "Int32"));
				result.Parameters.Add(new Parameter("PHOTO_VISIT", entity.PhotoVisit, "Int64"));
				result.Parameters.Add(new Parameter("VIDEO_VISIT", entity.VideoVisit, "Int64"));
				result.Parameters.Add(new Parameter("COST", entity.Cost, "Decimal"));
				result.Parameters.Add(new Parameter("VALUE", entity.Value, "Decimal"));

				result.OperationHandler = operationHandler;
			}

			return result;
		}

		/// <summary>
		/// 构建修改
		/// </summary>
		/// <param name="entity">实体</param>
		/// <returns>操作</returns>
		public virtual Operation BuildUpdate(Artwork entity)
		{
			Operation result = new Operation();

			if (entity != null)
			{
				NoQueryOperationHandler operationHandler = new NoQueryOperationHandler();

				result.Sql = @"
UPDATE Z_ARTWORK T
   SET T.PKID = :PKID,
       T.MUSEUM = :MUSEUM,
       T.NAME = :NAME,
       T.CODE = :CODE,
       T.ENGLISH_DESCRIPTION = :ENGLISH_DESCRIPTION,
       T.ENGLISH_REMARK = :ENGLISH_REMARK,
       T.CHINESE_DESCRIPTION = :CHINESE_DESCRIPTION,
       T.CHINESE_REMARK = :CHINESE_REMARK,
       T.PHOTO = :PHOTO,
       T.VIDEO = :VIDEO,
       T.FIRST_CARE_TIME = :FIRST_CARE_TIME,
       T.LAST_CARE_TIME = :LAST_CARE_TIME,
       T.FIRST_SHOW_TIME = :FIRST_SHOW_TIME,
       T.LAST_SHOW_TIME = :LAST_SHOW_TIME,
       T.CARE_TIMES = :CARE_TIMES,
       T.SHOW_TIMES = :SHOW_TIMES,
       T.PHOTO_VISIT = :PHOTO_VISIT,
       T.VIDEO_VISIT = :VIDEO_VISIT,
       T.COST = :COST,
       T.VALUE = :VALUE
 WHERE T.PKID = :PKID
";

				result.Parameters.Add(new Parameter("PKID", entity.Pkid, "Varchar2"));
				result.Parameters.Add(new Parameter("MUSEUM", entity.Museum, "Varchar2"));
				result.Parameters.Add(new Parameter("NAME", entity.Name, "NVarchar2"));
				result.Parameters.Add(new Parameter("CODE", entity.Code, "NVarchar2"));
				result.Parameters.Add(new Parameter("ENGLISH_DESCRIPTION", entity.EnglishDescription, "Clob"));
				result.Parameters.Add(new Parameter("ENGLISH_REMARK", entity.EnglishRemark, "Clob"));
				result.Parameters.Add(new Parameter("CHINESE_DESCRIPTION", entity.ChineseDescription, "NClob"));
				result.Parameters.Add(new Parameter("CHINESE_REMARK", entity.ChineseRemark, "NClob"));
				result.Parameters.Add(new Parameter("PHOTO", entity.Photo, "Blob"));
				result.Parameters.Add(new Parameter("VIDEO", entity.Video, "Blob"));
				result.Parameters.Add(new Parameter("FIRST_CARE_TIME", entity.FirstCareTime, "TimeStamp"));
				result.Parameters.Add(new Parameter("LAST_CARE_TIME", entity.LastCareTime, "TimeStamp"));
				result.Parameters.Add(new Parameter("FIRST_SHOW_TIME", entity.FirstShowTime, "Date"));
				result.Parameters.Add(new Parameter("LAST_SHOW_TIME", entity.LastShowTime, "Date"));
				result.Parameters.Add(new Parameter("CARE_TIMES", entity.CareTimes, "Int32"));
				result.Parameters.Add(new Parameter("SHOW_TIMES", entity.ShowTimes, "Int32"));
				result.Parameters.Add(new Parameter("PHOTO_VISIT", entity.PhotoVisit, "Int64"));
				result.Parameters.Add(new Parameter("VIDEO_VISIT", entity.VideoVisit, "Int64"));
				result.Parameters.Add(new Parameter("COST", entity.Cost, "Decimal"));
				result.Parameters.Add(new Parameter("VALUE", entity.Value, "Decimal"));
				result.Parameters.Add(new Parameter("PKID", entity.Pkid, "Varchar2"));
				
				result.OperationHandler = operationHandler;
			}

			return result;
		}

		/// <summary>
		/// 构建删除
		/// </summary>
		/// <param name="pkid">标识</param>
		/// <returns>操作</returns>
		public virtual Operation BuildDelete(string pkid)
		{
			Operation result = new Operation();

			NoQueryOperationHandler operationHandler = new NoQueryOperationHandler();

			result.Sql = @"
DELETE FROM Z_ARTWORK T WHERE T.PKID = :PKID
";
			result.Parameters.Add(new Parameter("PKID", pkid, "Varchar2"));
			result.OperationHandler = operationHandler;

			return result;
		}

		/// <summary>
		/// 构建查询
		/// </summary>
		/// <param name="first">开始行</param>
		/// <param name="last">结束行</param>
		/// <returns>操作</returns>
		public virtual Operation BuildSelect(int first, int last)
		{
			Operation result = new Operation();

			ArtworkOperationHandler operationHandler = new ArtworkOperationHandler();
			operationHandler.First = first;
			operationHandler.Last = last;

			result.Sql = @"
SELECT T.PKID AS T_PKID,
       T.MUSEUM AS T_MUSEUM,
       T.NAME AS T_NAME,
       T.CODE AS T_CODE,
       T.ENGLISH_DESCRIPTION AS T_ENGLISH_DESCRIPTION,
       T.ENGLISH_REMARK AS T_ENGLISH_REMARK,
       T.CHINESE_DESCRIPTION AS T_CHINESE_DESCRIPTION,
       T.CHINESE_REMARK AS T_CHINESE_REMARK,
       T.PHOTO AS T_PHOTO,
       T.VIDEO AS T_VIDEO,
       T.FIRST_CARE_TIME AS T_FIRST_CARE_TIME,
       T.LAST_CARE_TIME AS T_LAST_CARE_TIME,
       T.FIRST_SHOW_TIME AS T_FIRST_SHOW_TIME,
       T.LAST_SHOW_TIME AS T_LAST_SHOW_TIME,
       T.CARE_TIMES AS T_CARE_TIMES,
       T.SHOW_TIMES AS T_SHOW_TIMES,
       T.PHOTO_VISIT AS T_PHOTO_VISIT,
       T.VIDEO_VISIT AS T_VIDEO_VISIT,
       T.COST AS T_COST,
       T.VALUE AS T_VALUE
  FROM Z_ARTWORK T
";
			result.OperationHandler = operationHandler;

			return result;
		}

		/// <summary>
		/// 构建查询
		/// </summary>
		/// <param name="pkid">标识</param>
		/// <returns>操作</returns>
		public virtual Operation BuildSelect(string pkid)
		{
			Operation result = new Operation();

			result = this.BuildSelect(1, int.MaxValue);

			StringBuilder sqlBuilder = new StringBuilder();
			sqlBuilder.Append(result.Sql);

			string where = @"
WHERE T.PKID = :PKID
";
			sqlBuilder.Append(where);
		result.Parameters.Add(new Parameter("PKID", pkid, "Varchar2"));

			result.Sql = sqlBuilder.ToString();

			return result;
		}

		/// <summary>
		/// 构建查询
		/// </summary>
		/// <param name="entity">实体</param>
		/// <returns>操作</returns>
		public virtual Operation BuildSelectUnique(Artwork entity)
		{
			return null;
		}
	}
}
